-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,参数变更，hint变更，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
create extension gms_output;
set enable_func_cache = 1;
select gms_output.enable();
set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
set datestyle='ISO,MDY';
--创建表及函数
drop table if exists all_support_type;
create table all_support_type(
id int,
col_bool bool,
col_smallint smallint,
col_int int,
col_bigint bigint,
col_float float,
col_double_precision double precision,
col_char char(720),
col_varchar varchar(920),
col_varchar2 varchar2(20),
col_timestamp timestamp,
col_numeric numeric,
col_number number,
col_text text
);
insert into all_support_type values( 1, 'f',1,1,1,1,1,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values( 2, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 3, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values( 4, 'f',1,1,1,1,1,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 5, 'f',2,2,2,2,2,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 6, 'f',0,0,0,0,0,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',0,0,'1_haha');
insert into all_support_type values( 7, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values( 8, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values( 9, 'f',0,0,0,0,0,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(10, 'f',1,1,1,1,1,'0_haha','1_haha','0_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(11, 'f',2,2,2,2,2,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(12, 'f',0,0,0,0,0,'0_haha','0_haha','0_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(13, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(14, 'f',2,2,2,2,2,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',2,2,'1_haha');
insert into all_support_type values(15, 'f',0,0,0,0,0,'1_haha','0_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(16, 'f',1,1,1,1,1,'0_haha','1_haha','1_haha','2024-11-22 16:36:45',1,1,'1_haha');
insert into all_support_type values(17, 'f',2,2,2,2,2,'1_haha','0_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type values(18, 'f',0,0,0,0,0,'1_haha','1_haha','1_haha','2024-11-22 16:36:45',0,0,'0_haha');
insert into all_support_type values(19, 'f',1,1,1,1,1,'0_haha','0_haha','1_haha','2024-11-22 16:36:45',1,1,'0_haha');
insert into all_support_type values(20, 'f',2,2,2,2,2,'1_haha','1_haha','0_haha','2024-11-22 16:36:45',2,2,'0_haha');
insert into all_support_type(id) values(21);
insert into all_support_type(id) values(22);
insert into all_support_type values(23,'t',1111,1111,1111,1111,1111,'aasddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff','aaaaaa','fffff','1997-1-1 10:00:00',1,1,'3345');
insert into all_support_type values(24,'',1111,1111,1111,1111,1111,'','','','',1,1,'');
insert into all_support_type(id,col_varchar) values(11,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(12,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(100,'xxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
insert into all_support_type(id,col_varchar) values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx1');
select count(*) from (select * from all_support_type order by id) ;
--创建函数
create or replace function func_smallint_cache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
--case1
--set hint
select func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
select /*+ set(enable_func_cache off) */ func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
set enable_func_cache = 0;
select /*+ set(enable_func_cache on) */ func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
select /*+ set(enable_func_cache 1) */ func_smallint_cache(col_smallint) from (select * from all_support_type order by id) ;
--case2
--参数取值 不区分大小写
set enable_func_cache=1;
set enable_func_cache=0;
set enable_func_cache=2;
set ENABLE_FUNC_CACHE=on;
set enable_func_cache=off;
set enable_func_cache='ofF';
set enable_func_cache=OFF;
set enable_func_cache="OFF";
set enable_func_cache="oFF";
set enable_func_cache=true;
set enable_func_cache=false;
set enable_func_cache='true';
set enable_func_cache='false';
set ENABLE_FUNC_CACHE='True';
set enable_func_cache='TRUE';
set enable_func_cache='sss11';
set enable_func_cache='';
set enable_func_cache='null';
set "ENABLE_FUNC_CACHE"=1;
set "ENABLE_FUNC_CACHE"=default;
set enable_func_cache=default;
--case3
--case3-1
set enable_func_cache = 1;
create or replace function func_smallint_keyword(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable not result_cache;
/
select func_smallint_keyword(col_smallint) from (select * from all_support_type order by id) ;
--case3-2
create or replace function func_add_sql_oracle(a1 integer) RETURN integer immutable result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: execute function ...';
v_cnt:=a1+1;
RETURN v_cnt;
end;
/
select func_add_sql_oracle(col_smallint) from (select * from all_support_type order by id) ;
--case3-3
create or replace function func_add_sql_oracle_no(a1 integer) RETURN integer immutable not result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: execute function ...';
v_cnt:=a1+1;
RETURN v_cnt;
end;
/
select func_add_sql_oracle_no(col_smallint) from (select * from all_support_type order by id) ;
--出现位置
--case5
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql deterministic result_cache;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql result_cache deterministic;
/
--报错，预期成功
create or replace function func_smallint_keyword_2(p smallint) returns smallint result_cache deterministic as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint not result_cache deterministic as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql;
/
create or replace function func_add_sql_2(a1 integer) RETURN integer not result_cache stable IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: execute function ...';
v_cnt:=a1+1;
RETURN v_cnt;
end;
/
create or replace function func_add_sql_2(a1 integer) RETURN integer stable not result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: execute function ...';
v_cnt:=a1+1;
RETURN v_cnt;
end;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint result_cache stable as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint deterministic result_cache as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql;
/
create or replace function func_add_sql24(a1 integer, a2 integer) RETURN integer deterministic result_cache IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
--og报错，提交缺陷7481
create or replace function func_add_sql24(a1 integer, a2 integer) RETURN integer result_cache deterministic IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
create or replace function func_add_sql24(a1 integer, a2 integer) RETURN integer result_cache stable IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
create or replace function func_add_sql24(a1 integer, a2 integer) RETURN integer result_cache immutable IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
--新增关键字作为对象名
--case6
--case6-1 不带双引号
drop table if exists result_cache;
create table result_cache(id int,name varchar(40));
drop table if exists result_cache;
drop table if exists tt;
create table tt(id int,name varchar(40));
create view result_cache as select * from tt;
drop view if exists result_cache;
--case6-2 带双引号
drop table if exists "RESULT_CACHE";
create table "RESULT_CACHE"(id int,name varchar(40),c1 int);
drop table if exists "not RESULT_CACHE";
create table "not RESULT_CACHE"(id int,name varchar(40),c2 int);
drop table if exists "not result_cache";
create table "not result_cache"(id int,name varchar(40),c2 int);
--来回切换参数
--case7
set enable_func_cache = 1;
create or replace function func_add_para(a1 integer) RETURN integer immutable result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: execute function ...';
v_cnt:=a1+1;
RETURN v_cnt;
end;
/
select func_add_sql_oracle(col_smallint) from (select * from all_support_type order by id) ;
set enable_func_cache = 0;
select func_add_sql_oracle(col_smallint) from (select * from all_support_type order by id) ;
set enable_func_cache = 1;
select func_add_sql_oracle(col_smallint) from (select * from all_support_type order by id) ;
--case8
--case8-1 可以查询到
create or replace function func_add_10(a1 integer, a2 integer) RETURN integer immutable result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: excute function...';
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_add_10';
--case8-2 查询不到
create or replace function func_add_11(a1 integer, a2 integer) RETURN integer result_cache IS
v_cnt integer;
BEGIN
raise notice 'NOTICE: excute function...';
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_add_11';
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid WHERE p.proname = 'func_add_11';
--更改函数属性
--case9
--case9-1
create or replace function func_property(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_property(p smallint) not result_cache;
select func_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_property(p smallint) result_cache;
select func_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_property(p smallint) volatile;
select func_property(col_smallint) from (select * from all_support_type order by id) ;
--未走结果缓存，7497
alter function func_property(p smallint) stable result_cache;
select func_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_property(p smallint) deterministic;
select func_property(col_smallint) from (select * from all_support_type order by id) ;
--case9-2
set enable_func_cache = 1;
create or replace function func_add_property(p smallint) RETURN smallint immutable result_cache IS
v_cnt smallint;
BEGIN
raise notice 'NOTICE: excute function...';
v_cnt:=p+100;
RETURN v_cnt;
end;
/
select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
--预期不走结果缓存，实际走了OG，7497
alter function func_add_property(p smallint) not result_cache;
select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_add_property(p smallint) result_cache;
select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
--alter function func_add_property(p smallint) volatile;
--select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
--alter function func_add_property(p smallint) stable;
--select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
alter function func_add_property(p smallint) deterministic;
select func_add_property(col_smallint) from (select * from all_support_type order by id) ;
drop table if exists all_support_type;
